home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Freelog Special Freeware 31
/
FreelogHS31.iso
/
ArgentCompta
/
FASTCAISSE
/
Setup FASTCAISSE.exe
/
{app}
/
A faire61.sql
< prev
next >
Wrap
Text File
|
2004-05-16
|
4KB
|
147 lines
ALTER TABLE LIGNES ADD QTE_LIVRE DECIMAL(11,2);
UPDATE LIGNES SET QTE_LIVRE=QTE_INITIALE-QUANTITE WHERE QTE_INITIALE IS NOT NULL AND QUANTITE IS NOT NULL;
UPDATE LIGNES SET QUANTITE=QTE_INITIALE WHERE QTE_INITIALE IS NOT NULL;
ALTER TABLE LIGNES DROP QTE_INITIALE;
ALTER TABLE PIECES ALTER REMISE TO REMISEHT;
ALTER PROCEDURE "MAJSTOCK"
(
"INDEXP" INTEGER,
"DELTA" SMALLINT
)
AS
DECLARE VARIABLE V1 SMALLINT;
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE Q DECIMAL(11,2);
DECLARE VARIABLE Q1 DECIMAL(11,2);
BEGIN
SELECT TYPESTOCK FROM PIECES WHERE INDX=:INDEXP AND TYPESTOCK<>1 AND STATUT IN (0,3) INTO V1;
IF (V1 IS NOT NULL) THEN
FOR SELECT INDEXARTICLE,SUM(QUANTITE),SUM(QTE_LIVRE) FROM LIGNES
JOIN ARTICLES A ON A.INDX=INDEXARTICLE AND A.SUIVI<>0
WHERE INDEXPIECE=:INDEXP
AND INDEXARTICLE IS NOT NULL AND INDEXARTICLE>0
AND QUANTITE IS NOT NULL AND QUANTITE<>0
GROUP BY INDEXARTICLE
INTO :I,:Q,:Q1
DO
BEGIN
IF (Q1 IS NULL) THEN Q1=0;
IF (Q IS NULL) THEN Q=0;
Q =(Q-Q1)*:DELTA;
IF (Q<>0) THEN
BEGIN
IF (V1=4) THEN V1=0;
/* TICKET, FACTURE */
IF (V1=0) THEN UPDATE ARTICLES SET DISPO=DISPO-:Q WHERE INDX=:I;
/* RESERVATION */
ELSE IF (V1=2) THEN UPDATE ARTICLES SET DISPO=DISPO-:Q,RESERVE=RESERVE+:Q WHERE INDX=:I;
/* BL */
ELSE IF (V1=3) THEN UPDATE ARTICLES SET DISPO=DISPO-:Q,BL_CLIENT=BL_CLIENT+:Q WHERE INDX=:I;
/* PRET */
ELSE IF (V1=5) THEN UPDATE ARTICLES SET DISPO=DISPO-:Q,EN_PRET=EN_PRET+:Q WHERE INDX=:I;
/* SAV */
ELSE IF (V1=6) THEN UPDATE ARTICLES SET EN_SAV=EN_SAV+:Q WHERE INDX=:I;
/* RETOUR SAV */
ELSE IF (V1=7) THEN UPDATE ARTICLES SET EN_SAV=EN_SAV-:Q WHERE INDX=:I;
/* AVOIR */
ELSE IF (V1=8) THEN UPDATE ARTICLES SET DISPO=DISPO+:Q WHERE INDX=:I;
/* COMMANDE */
ELSE IF (V1=13) THEN UPDATE ARTICLES SET EN_COMMANDE=EN_COMMANDE+:Q WHERE INDX=:I;
/* LIVRAISON */
ELSE IF (V1=14) THEN UPDATE ARTICLES SET DISPO=DISPO+:Q WHERE INDX=:I;
END
END /* DO */
END
;
/* Decide si une piece est verouille (statut=1) ou non (statut=0) */
CREATE PROCEDURE "SET_VERROU"
(
"INDXPIECE" INTEGER,
"PLIVRE" SMALLINT,
"REGROUPE" SMALLINT,
"PAYE" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE V_Z SMALLINT;
DECLARE VARIABLE V_SOLDE DECIMAL(14,4);
DECLARE VARIABLE V_LIVRE SMALLINT;
DECLARE VARIABLE V_GROUP INTEGER;
DECLARE VARIABLE V_STAT SMALLINT;
DECLARE VARIABLE NEW_STAT SMALLINT;
BEGIN
SELECT Z,SOLDE,LIVRE,REGROUPE_DANS,STATUT FROM PIECES WHERE INDX=:INDXPIECE
INTO :V_Z,:V_SOLDE,:V_LIVRE,V_GROUP,V_STAT;
/* SEUL LES PIECES NORMALES (STATUT=0) OU ARCHIVEES (STATUT=1) SONT A FAIRE */
IF (V_STAT>1) THEN EXIT;
NEW_STAT=0;
/* CONDITION DE VERROUILLAGE */
IF (
(:PAYE=1 AND V_Z=1 AND V_SOLDE=0)
OR
(:PLIVRE=1 AND V_LIVRE=1)
OR
(REGROUPE=1 AND V_GROUP>0)
) THEN
BEGIN
NEW_STAT=1;
/*CONDITION DE DEVERROUILLAGE */
IF (
(:PAYE=1 AND V_Z=1 AND V_SOLDE<>0)
OR
(:PLIVRE=1 AND V_LIVRE=0)
) THEN NEW_STAT=0;
END
IF (NEW_STAT<>V_STAT) THEN UPDATE PIECES SET STATUT=:NEW_STAT WHERE INDX=:INDXPIECE;
EXIT;
end
;
CREATE PROCEDURE "ALL_VERROU"
(
"LIVRE" SMALLINT,
"REGROUPE" SMALLINT,
"PAYE" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
FOR SELECT INDX FROM PIECES INTO :N DO EXECUTE PROCEDURE SET_VERROU(:N,:LIVRE,:REGROUPE,:PAYE);
EXIT;
end
;
ALTER PROCEDURE "SET_LIVRE"
(
"INDXPIECE" INTEGER
)
AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE OLD_L SMALLINT;
DECLARE VARIABLE NEW_L SMALLINT;
BEGIN
SELECT TYPEPIECE,LIVRE FROM PIECES WHERE INDX=:INDXPIECE INTO :N,:OLD_L;
IF (N IN (2,3,13)) THEN /* TESTER SI QTE */
begin
SELECT COUNT(*) FROM LIGNES
WHERE INDEXPIECE=:INDXPIECE AND QUANTITE IS NOT NULL AND QTE_LIVRE IS NOT NULL AND QUANTITE<>QTE_LIVRE
INTO :N;
/* N=0 donc pas de lignes ou la quantitΘ livrΘ diffΦre de la quantitΘ initiale, donc piΦce livrΘe */
IF (N=0) THEN NEW_L=1; ELSE NEW_L=0;
END
ELSE NEW_L=NULL;
IF (OLD_L IS NULL AND NEW_L IS NULL) THEN EXIT;
IF (OLD_L IS NULL OR NEW_L IS NULL OR (OLD_L<>NEW_L)) THEN UPDATE PIECES SET LIVRE=:NEW_L WHERE INDX=:INDXPIECE;
end
;
EXECUTE PROCEDURE ALL_LIVRE;
EXECUTE PROCEDURE ALL_VERROU(1,1,1);
SET GENERATOR VERSION_BASE TO 61;